package com.sailfish.springbootdemo.dao.db1;

import com.sailfish.springbootdemo.common.DB1TableName;
import com.sailfish.springbootdemo.pojo.db1.RoadmapH3c;
import org.apache.ibatis.annotations.*;
import org.apache.ibatis.type.JdbcType;

import java.util.List;

public interface RoadmapH3cDao {

    @Results(id = "roadmapH3cResultMap", value = {
            @Result(column = "roadmap_h3c_param_id", property = "roadmapH3cParamId", jdbcType = JdbcType.INTEGER),
            @Result(column = "roadmap_h3c_param_key", property = "roadmapH3cParamKey", jdbcType = JdbcType.VARCHAR),
            @Result(column = "roadmap_h3c_param_value1", property = "roadmapH3cParamValue1", jdbcType = JdbcType.VARCHAR),
            @Result(column = "roadmap_h3c_param_value2", property = "roadmapH3cParamValue2", jdbcType = JdbcType.VARCHAR),
            @Result(column = "roadmap_h3c_param_value3", property = "roadmapH3cParamValue3", jdbcType = JdbcType.VARCHAR),
            @Result(column = "status", property = "status", jdbcType = JdbcType.INTEGER),
            @Result(column = "sort", property = "sort", jdbcType = JdbcType.INTEGER),
            @Result(column = "create_time", property = "createTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "update_time", property = "updateTime", jdbcType = JdbcType.DATETIMEOFFSET),
            @Result(column = "year", property = "year", jdbcType = JdbcType.INTEGER),
            @Result(column = "version_y", property = "versionY", jdbcType = JdbcType.INTEGER),
            @Result(column = "version_n", property = "versionN", jdbcType = JdbcType.VARCHAR),
    })
    @Select("<script>" +
            "select * from " + DB1TableName.ROADMAP_H3C + " where 1 = 1" +
            "<if test='year != null and year != \"\" '>" +
            " and year = #{year}" +
            "</if>" +
            "</script>")
    List<RoadmapH3c> getAllRoadmapH3c(@Param("year") Integer year, @Param("versionY") Integer versionY, @Param("versionN") String versionN);

    /**
     * 根据key 和 year查询
     *
     * @param year
     * @param roadmapH3cParamKey
     * @return
     */
    @ResultMap(value = "roadmapH3cResultMap")
    @Select("select * from " + DB1TableName.ROADMAP_H3C + " where year = #{year} and roadmap_h3c_param_key = #{roadmapH3cParamKey} order by create_time desc limit 1")
    RoadmapH3c getRoadmapH3cByYearAndKey(@Param("year") String year, @Param("roadmapH3cParamKey") String roadmapH3cParamKey, @Param("versionY") Integer versionY, @Param("versionN") String versionN);

    /**
     * 根据key查询
     *
     * @param roadmapH3cParamKey
     * @return
     */
    @ResultMap(value = "roadmapH3cResultMap")
    @Select("select * from " + DB1TableName.ROADMAP_H3C + " where roadmap_h3c_param_key = #{roadmapH3cParamKey} and year = #{year} and version_y = #{versionY} and version_n = #{versionN} order by create_time desc limit 1")
    RoadmapH3c getRoadmapH3cByKey(@Param("roadmapH3cParamKey") String roadmapH3cParamKey, @Param("year") Integer year, @Param("versionY") Integer versionY, @Param("versionN") String versionN);

    /**
     * 根据key删除
     */
    @Delete("delete from " + DB1TableName.ROADMAP_H3C + " where roadmap_h3c_param_key = #{roadmapH3cParamKey}  and year = #{year} and version_y = #{versionY} and version_n = #{versionN}")
    Integer deleteRoadmapH3cByKey(@Param("roadmapH3cParamKey") String roadmapH3cParamKey, @Param("year") Integer year, @Param("versionY") Integer versionY, @Param("versionN") String versionN);

    /**
     * 新增
     */
    @Insert("insert into " + DB1TableName.ROADMAP_H3C + "(roadmap_h3c_param_key,create_time,sort,status,roadmap_h3c_param_value1,roadmap_h3c_param_value2,roadmap_h3c_param_value3,year,version_y,version_n) values " +
            "(#{roadmapH3c.roadmapH3cParamKey},#{roadmapH3c.createTime},#{roadmapH3c.sort},#{roadmapH3c.status},#{roadmapH3c.roadmapH3cParamValue1},#{roadmapH3c.roadmapH3cParamValue2},#{roadmapH3c.roadmapH3cParamValue3},#{roadmapH3c.year},#{roadmapH3c.versionY},#{roadmapH3c.versionN})")
    Integer addRoadmapH3c(@Param("roadmapH3c") RoadmapH3c roadmapH3c);

    /**
     * 查看历史版本
     *
     * @return
     */
    @ResultMap(value = "roadmapH3cResultMap")
    @Select("select distinct version_y,version_n from " + DB1TableName.ROADMAP_H3C + " order by version_y, CONVERT(SUBSTRING(version_n, 2), UNSIGNED INTEGER)")
    List<RoadmapH3c> getHistoryVersion();

    /**
     * 根据年份查询最大的历史版本
     *
     * @return
     */
    @ResultMap(value = "roadmapH3cResultMap")
    @Select("select distinct version_n from " + DB1TableName.ROADMAP_H3C + " where version_y = #{versionY} order by CONVERT(SUBSTRING(version_n, 2), UNSIGNED INTEGER) desc limit 1")
    RoadmapH3c getMaxHistoryVersionByYear(@Param("versionY") Integer versionY);

    /**
     * 批量插入
     *
     * @param list
     * @return
     */
    @Insert("<script>" +
            "insert into " + DB1TableName.ROADMAP_H3C + " (roadmap_h3c_param_key,roadmap_h3c_param_value1,create_time,sort,status,roadmap_h3c_param_value2,roadmap_h3c_param_value3,year,version_y,version_n) values " +
            "<foreach collection=\"list\" separator=\",\" item=\"item\">\n" +
            "            (#{item.roadmapH3cParamKey},#{item.roadmapH3cParamValue1},#{item.createTime},#{item.sort},#{item.status},#{item.roadmapH3cParamValue2},#{item.roadmapH3cParamValue3},#{item.year},#{item.versionY},#{item.versionN})\n" +
            "        </foreach>" +
            "</script>")
    Integer batchAddProjectNode(@Param("list") List<RoadmapH3c> list);

    /**
     * 更新
     */
    @Update("<script>" +
            "update " + DB1TableName.ROADMAP_H3C +
            "<set>" +
            "<if test='roadmapH3c.roadmapH3cParamKey != null and roadmapH3c.roadmapH3cParamKey != \"\" '>" +
            " roadmap_h3c_param_key = #{roadmapH3c.roadmapH3cParamKey}," +
            "</if>" +
            "<if test='roadmapH3c.roadmapH3cParamValue1 != null '>" +
            " roadmap_h3c_param_value1 = #{roadmapH3c.roadmapH3cParamValue1}," +
            "</if>" +
            "<if test='roadmapH3c.roadmapH3cParamValue2 != null '>" +
            " roadmap_h3c_param_value2 = #{roadmapH3c.roadmapH3cParamValue2}," +
            "</if>" +
            "<if test='roadmapH3c.roadmapH3cParamValue3 != null '>" +
            " roadmap_h3c_param_value3 = #{roadmapH3c.roadmapH3cParamValue3}," +
            "</if>" +
            "<if test='roadmapH3c.updateTime != null '>" +
            " update_time = #{roadmapH3c.updateTime}," +
            "</if>" +
            "<if test='roadmapH3c.sort != null and roadmapH3c.sort != \"\" '>" +
            " sort = #{roadmapH3c.sort}," +
            "</if>" +
            "<if test='roadmapH3c.status != null and roadmapH3c.status != \"\" '>" +
            " status = #{roadmapH3c.status}," +
            "</if>" +
            "<if test='roadmapH3c.year != null and roadmapH3c.year != \"\" '>" +
            " year = #{roadmapH3c.year}," +
            "</if>" +
            "<if test='roadmapH3c.versionY != null and roadmapH3c.versionY != \"\" '>" +
            " version_y = #{roadmapH3c.versionY}," +
            "</if>" +
            "<if test='roadmapH3c.versionN != null and roadmapH3c.versionN != \"\" '>" +
            " version_n = #{roadmapH3c.versionN}," +
            "</if>" +
            "</set>" +
            " where roadmap_h3c_param_id = #{roadmapH3c.roadmapH3cParamId}" +
            "</script>")
    Integer updateRoadmapH3c(@Param("roadmapH3c") RoadmapH3c roadmapH3c);
}
